from sqlalchemy import create_engine, select, insert, update, bindparam, delete
from sqlalchemy.orm import sessionmaker, declarative_base
from db_base_orm import Session, Department, Member


def batch_insert(session):
    session.execute(
      insert(Department).values(
        [
          {"name": "部门1"},
          {"name": "部门2"},
          {"name": "部门3"}
        ]
      )
    )
    session.commit()

# 使用嵌套select函数来批量插入
def batch_orm_insert(session):
    session.execute(
      insert(Member).values(
        [
          {
            "department_id": select(Department.id).where(Department.name == "部门1"),
            "name": '用户1',
            "birthday": '2019-10-10'
          },
          {
            "department_id": select(Department.id).where(Department.name == "部门1"),
            "name": '用户2',
            "birthday": '2020-10-10'
          },
        ]
      )
    )
    session.commit()


# 批量更新
def batch_update(session):
    session.execute(
      update(Member),
      [
        {"id": 2, "name": '用户22'},
        {"id": 3, "birthday": "2024-04-04"}
      ]
    )
    session.commit()


# 批量删除
def batch_delete(session):
    session.execute(
      delete(Member).where(Member.name.in_(['用户111', '用户222']))
    )
    session.commit()






session = Session()
# batch_insert(session)
# batch_orm_insert(session)
batch_update(session)
